import xlrd
from pandas import DataFrame
from xlutils.copy import copy
import pandas as pd

parentPath = r'C:\Users\da\Desktop\\'  # 全局父路径
parentTitle = r'商品名称'  # 第一行商品名称需要去除


def changePrice():
    # 第一步
    # 设置代发价
    # 代发价为空时，取规格1的价格

    rowIndex = 0
    daifaprice = 8  # 代发价
    guigeprice = 13  # 规格1价格
    rsheet, wb, ws = openExcel('2022.xls', 0)
    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            if row[daifaprice].value == "":
                ws.write(rowIndex, daifaprice, row[guigeprice].value)
                print("第" + str(rowIndex) + "行为空")
        rowIndex = rowIndex + 1
    wb.save(parentPath + '代发价.xls')
    print("结束")


def suggestPrice():
    # 第二步
    # 设置建议价
    # 建议价为空时取代发价价格

    rowIndex = 0
    sugprice = 7  # 建议价所在列
    daifaprice = 8  # 代发价所在列
    rsheet, wb, ws = openExcel('代发价.xls', 0)
    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            if row[sugprice].value == "":
                if row[daifaprice].value == "":
                    print("代发价_第" + str(rowIndex) + "行为空")
                else:
                    ws.write(rowIndex, sugprice, priceGap(int(row[daifaprice].value)))
        rowIndex = rowIndex + 1
    wb.save(parentPath + '建议价.xls')

    # 插入市场价和本店价
    df = pd.read_excel(parentPath + '建议价.xls')
    df.insert(sugprice, '市场价', None)
    DataFrame(df).to_excel(parentPath + '建议价.xls', sheet_name='Sheet1', index=False, header=True)
    print("结束")


def shopPrice():
    # 第三步
    # 设置市场价
    # 市场价在建议价的基础上加价

    rowIndex = 0
    sugprice = 8  # 建议价所在列
    marketprice = 7  # 市场价所在列
    rsheet, wb, ws = openExcel('建议价.xls', 0)
    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            ws.write(rowIndex, marketprice, priceGap(float(row[sugprice].value)))
        rowIndex = rowIndex + 1
    wb.save(parentPath + '市场价.xls')
    print("结束")


def realSellPrice():
    # 第四步
    # 规格真实售价

    rowIndex = 0
    countsell = 10  # 起批量
    guigename1 = 13  # 规格1名称
    guigename2 = 15  # 规格2名称
    guigename3 = 17  # 规格3名称
    guigeprice1 = 14  # 规格1价格
    guigeprice2 = 16  # 规格2价格
    guigeprice3 = 18  # 规格3价格
    rsheet, wb, ws = openExcel('起批价.xls', 0)
    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            if row[guigename1].value != "":
                ws.write(rowIndex, guigeprice1, priceGap(float(row[guigeprice1].value)))
            if row[guigename2].value != "":
                ws.write(rowIndex, guigeprice2, priceGap(float(row[guigeprice2].value)))
            if row[guigename3].value != "":
                ws.write(rowIndex, guigeprice3, priceGap(float(row[guigeprice3].value)))
        rowIndex = rowIndex + 1
    wb.save(parentPath + '最终售价.xls')
    print("结束")


def startSellPrice():
    # 第五步
    # 起批量和起批价格修改

    rowIndex = 0
    countsell = 10  # 起批量
    guigename1 = 13  # 规格1名称
    guigename2 = 15  # 规格2名称
    guigename3 = 17  # 规格3名称
    guigeprice1 = 14  # 规格1价格
    guigeprice2 = 16  # 规格2价格
    guigeprice3 = 18  # 规格3价格
    rsheet, wb, ws = openExcel('市场价.xls', 0)
    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            if row[countsell].value > 1:
                if row[guigename1].value != "":
                    ws.write(rowIndex, guigename1, row[guigename1].value + "*" + str(int(row[countsell].value)))
                    ws.write(rowIndex, guigeprice1, str(row[guigeprice1].value * int(row[countsell].value)))
                if row[guigename2].value != "":
                    ws.write(rowIndex, guigename2, row[guigename2].value + "*" + str(int(row[countsell].value)))
                    ws.write(rowIndex, guigeprice2, str(row[guigeprice2].value * int(row[countsell].value)))
                if row[guigename3].value != "":
                    ws.write(rowIndex, guigename3, row[guigename3].value + "*" + str(int(row[countsell].value)))
                    ws.write(rowIndex, guigeprice3, str(row[guigeprice3].value * int(row[countsell].value)))
        rowIndex = rowIndex + 1
    wb.save(parentPath + '起批价.xls')
    print("结束")


def openExcel(fileName, sheet):
    # Excel打开操作

    rb = xlrd.open_workbook(parentPath + fileName)
    rb.sheets()
    rsheet = rb.sheet_by_index(sheet)

    wb = copy(rb)
    ws = wb.get_sheet(sheet)
    return rsheet, wb, ws


def priceGap(oldPrice):
    # 设置盈利空间

    if oldPrice < 10:
        oldPrice = oldPrice + 3
    elif 10 <= oldPrice < 50:
        oldPrice = oldPrice + 5
    elif 50 <= oldPrice < 80:
        oldPrice = oldPrice + 10
    elif 80 <= oldPrice:
        oldPrice = oldPrice + 15
    return oldPrice


if __name__ == '__main__':
    # changePrice()
    # suggestPrice()
    # shopPrice()
    # startSellPrice()
    realSellPrice()
